package cn.drenal.helper;

import cn.drenal.common.Constants;
import cn.drenal.common.Main;
import cn.drenal.pet.ItemPet;
import cn.drenal.pet.PetAnimal;
import cn.drenal.user.User;

import javax.swing.*;
import java.sql.*;
import java.text.DecimalFormat;
import java.util.ArrayList;

public class JDBCHelper
{
    private static Connection getConnectionMYSQL()
    {        //连接数据库对象
        Connection conn = null;
        try
        {
            Class.forName(Constants.DATABASE_DRIVER);
            conn = DriverManager.getConnection(Constants.DATABASE_URL, Constants.DATABASE_USERNAME, Constants.DATABASE_PASSWORD);
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (SQLException e)
        {
            JOptionPane.showMessageDialog(null, "无法连接到数据库，请检查数据库用户名密码是否设置正确！", "Error", JOptionPane.WARNING_MESSAGE);
            e.printStackTrace();
        }
        return conn;
    }

    public static void close(Connection conn)
    {         // 关闭程序
        if (conn != null)
        {
            try
            {
                conn.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    public static ArrayList<PetAnimal> queryAllData()
    {
        Connection conn = null;    /*  和数据库建立连接    */
        Statement stmt = null;     /*  提供想数据库发送执行语句和获取结果的方法    */
        ResultSet rs = null;       /*  创建存放从数据库中得到的结果的对象    */
        ArrayList<PetAnimal> list = new ArrayList<PetAnimal>();
        try
        {
            conn = getConnectionMYSQL();
            stmt = conn.createStatement();         /*  向数据库发送执行语句和获取结果的方法并储存在stmt中 */
            String sql = "select num, type, breed, price, quantity from Pet;";
            rs = stmt.executeQuery(sql);//把数据库访问查找的结果放在rs中 让我们使用

            while (rs.next())
            {
                PetAnimal thisPet = new PetAnimal();
                thisPet.setNum(rs.getString("num"));
                thisPet.setType(rs.getString("type"));
                thisPet.setBreed(rs.getString("breed"));
                thisPet.setPrice(rs.getDouble("price"));
                thisPet.setQuantity(rs.getInt("quantity"));
                list.add(thisPet);              //将thisPet 结果添加到list集合中
            }
            return list;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {                            //无论怎么样都要关闭conn连接
            close(conn);
        }
        return null;
    }

    //添加数据方法
    public static boolean addPetAnimal(PetAnimal animal)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try
        {
            conn = getConnectionMYSQL();         // 连接数据库
            String sql = "insert into Pet (num,type,breed,price,quantity) values(?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);  // 对传进来的sql语句进行预编译

            pstmt.setString(1, animal.getNum());
            pstmt.setString(2, animal.getType());
            pstmt.setString(3, animal.getBreed());
            pstmt.setDouble(4, animal.getPrice());
            pstmt.setDouble(5, animal.getQuantity());

            //executeUpdate()  用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句
            if (pstmt.executeUpdate() > 0)
            {
                result = true;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);         //关闭数据库
        }

        return result;
    }

    //删除数据
    public static boolean delPetAnimal(String delNum)
    {
        boolean result = false;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try
        {
            conn = getConnectionMYSQL();
            String sql = "delete from Pet where num = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, delNum);
            if (pstmt.executeUpdate() > 0)
            {
                result = true;
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);                //关闭数据库
        }
        return result;
    }

    //修改数据
    public static boolean updatePetAnimal(PetAnimal animal, String preNum)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        try
        {
            conn = getConnectionMYSQL();
            String sql = "update Pet\n" +
                    "set num = ?, type = ?, breed = ?, price = ?, quantity = ?\n" +
                    "where num = ?;";
            pstmt = conn.prepareStatement(sql);
            //进行预处理
            pstmt.setString(1, animal.getNum());
            pstmt.setString(2, animal.getType());
            pstmt.setString(3, animal.getBreed());
            pstmt.setDouble(4, animal.getPrice());
            pstmt.setDouble(5, animal.getQuantity());
            pstmt.setString(6, preNum);
            if (pstmt.executeUpdate() > 0)
            {
                result = true;
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);
        }
        return result;
    }

    //筛选出所有的种类
    public static ArrayList<String> selectPetAnimalType()
    {
        boolean result = false;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<String> dataList = new ArrayList<>();

        try
        {
            conn = getConnectionMYSQL();
            String sql = "select distinct type\n" +
                    "from Pet;";
            stmt = conn.prepareStatement(sql);
            //pstmt.setString(1, selTag);
            rs = stmt.executeQuery(sql);

            while (rs.next())
            {
                dataList.add(rs.getString("type"));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);
        }

        return dataList;
    }

    //按照宠物种类进行筛选
    public static ArrayList<PetAnimal> selectPetAnimalWithType(String type)
    {
        boolean result = false;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<PetAnimal> dataList = new ArrayList<>();

        try
        {
            conn = getConnectionMYSQL();
            String sql = "select num, type, breed, price, quantity from Pet where Pet.type = \"" + type + "\";";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery(sql);

            while (rs.next())
            {
                PetAnimal thisPet = new PetAnimal();
                thisPet.setNum(rs.getString("num"));
                thisPet.setType(rs.getString("type"));
                thisPet.setBreed(rs.getString("breed"));
                thisPet.setPrice(rs.getDouble("price"));
                thisPet.setQuantity(rs.getInt("quantity"));
                dataList.add(thisPet);              //讲thisPet 结果添加到list集合中
            }
            return dataList;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);
        }

        return dataList;
    }

    //筛选出所有的用户
    public static ArrayList<User> selectUsr()
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<User> dataList = new ArrayList<>();
        try
        {
            conn = getConnectionMYSQL();
            String sql = "select usrname ,psw ,lvl ,balance\n" +
                    "from Usr;";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next())
            {
                dataList.add(new User(
                        rs.getString("usrname"),
                        rs.getString("psw"),
                        rs.getInt("lvl"),
                        rs.getDouble("balance")
                ));
            }
            return dataList;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);
        }
        return null;
    }

    //是否存在用户
    public static boolean isUsrExist(String usrName, ArrayList<User> users)
    {
        for (int i = 0; i < users.size(); i++)
        {
            if (usrName.equals(users.get(i).getUsrname()))
            {//注意getUSername
                return true;
            }
        }
        return false;
    }

    //是否为管理员
    public static boolean isAdministrator(User usr)
    {
        return usr.getLvl() == Constants.LVL_USR_ADMIN;
    }

    //psw correct
    //TODO 待优化
    public static boolean isPwsCorrect(String username, String currentPsw, ArrayList<User> usrs)
    {
        for (int i = 0; i < usrs.size(); i++)
        {
            if (username.equals(usrs.get(i).getUsrname()))
            {
                return usrs.get(i).getUsrPsw().equals(currentPsw);
            }
        }
        return false;//没找着
    }

    //注册账号
    public static boolean registryUsr(User usr)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try
        {
            conn = getConnectionMYSQL();
            String sql = "insert into Usr (usrname, psw, balance, lvl) values(?, ?, ?, ?)";
            pstmt = conn.prepareCall(sql);
            pstmt.setString(1, usr.getUsrname());
            pstmt.setString(2, usr.getUsrPsw());
            pstmt.setDouble(3, usr.getBalance());
            pstmt.setInt(4, usr.getLvl());

            JOptionPane.showMessageDialog(null, "注册成功！", "提示信息", JOptionPane.INFORMATION_MESSAGE);
            Main.consoleLog("注册成功。");
            if (pstmt.executeUpdate() > 0)
            {
                result = true;
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);
        }

        return result;
    }

    //购买方法
    public static void deposit(User usr, PetAnimal petAnimal, int qtt)
    {
        Connection conn = null;
        Statement stmt = null;
        DecimalFormat df = new DecimalFormat("#.##");
        try
        {
            conn = getConnectionMYSQL();
            //让Pet表的数量-1
            String sql = "update Pet set quantity = " +
                    (petAnimal.getQuantity() - qtt) +
                    " where num = \'" +
                    petAnimal.getNum() + "\';";
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            petAnimal.setQuantity(petAnimal.getQuantity() - qtt);
            //修改用户表的账户余额
            sql = "update Usr " +
                    "set balance = " +
                    (usr.getBalance() - petAnimal.getPrice() * qtt) +
                    " where usrname = \"" +
                    usr.getUsrname() +
                    "\";";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate(sql);
            usr.setBalance(Double.parseDouble(df.format(usr.getBalance() - petAnimal.getPrice() * qtt)));

        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);
        }
    }

    //充值方法
    public static void withdraw(User usr, double money)
    {
        Connection conn = null;
        Statement stmt = null;
        DecimalFormat df = new DecimalFormat("#.##");
        try
        {
            conn = getConnectionMYSQL();
            String sql;
            //修改用户表的账户余额
            sql = "update Usr " +
                    "set balance = " +
                    (usr.getBalance() + money) +
                    " where usrname = \"" +
                    usr.getUsrname() +
                    "\";";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate(sql);
            usr.setBalance(Double.parseDouble(df.format(usr.getBalance() + money)));
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(conn);
        }
    }

    //集合数组转为二维数组
    public static String[][] listToArray(ArrayList<PetAnimal> list)
    {
        String[][] tbody = new String[list.size()][5];
        for (int i = 0; i < list.size(); i++)
        {
            PetAnimal pet = list.get(i);
            tbody[i][0] = pet.getNum();
            tbody[i][1] = pet.getType();
            tbody[i][2] = pet.getBreed();
            tbody[i][3] = pet.getPrice() + "";
            tbody[i][4] = pet.getQuantity() + "";
        }
        return tbody;
    }

    //TODO 重载出现问题了
    public static String[][] listToArrayItemPet(ArrayList<ItemPet> list)
    {
        String[][] tbody = new String[list.size()][4];
        for (int i = 0; i < list.size(); i++)
        {
            ItemPet item = list.get(i);
            tbody[i][0] = item.getBreed();
            tbody[i][1] = item.getPrice() + "";
            tbody[i][2] = item.getQuantity() + "";
            tbody[i][3] = item.getPrice() * item.getQuantity() + "";
        }
        return tbody;
    }
}

